# -*- coding: utf-8 -*-#
__author__ = 'yangq'
import os
from tornado import gen
from common import timetool
from appcommon.appinstance import mysql_app_pool
import settings
import re
from bs4 import BeautifulSoup


@gen.coroutine
def save_circle_info_2_db(phone, content, longitude, latitude, provice, city, area, address, images, current_time, belong_area, couch):
    images_str = ''
    for item in images:
        images_str += item + ' '
    sql = (
        "insert into app_circle_info (phone, text, longitude, latitude, provice, city, area, address, "
        u"images, create_time, belong_area, couch) values ('{}','{}','{}','{}','{}','{}', '{}','{}','{}',{}, '{}', {})"
    )
    yield mysql_app_pool.insert_one(sql.format(
        phone, content, longitude, latitude, provice, city, area, address, images_str, current_time, belong_area, couch
    ))
    res = True
    raise gen.Return(res)


@gen.coroutine
def save_circle_reply_2_db(c_id, phone, content, images, current_time, to_reply_id, to_phone, couch):
    images_str = ''
    for item in images:
        images_str += item + ' '
    sql = (
        "insert into app_circle_reply (c_id, phone, content, "
        u"images, create_time, to_reply_id, to_phone, couch) values ({},'{}','{}','{}',{},{}, '{}', {})"
    )

    yield mysql_app_pool.insert_one(sql.format(
        c_id, phone, content, images_str, current_time, to_reply_id, to_phone, couch
    ))
    res = True
    raise gen.Return(res)


@gen.coroutine
def get_reply_id(c_id, phone, current_time):
    sql = (
        "select id from app_circle_reply where c_id={} and phone='{}' and create_time={}"
    )
    res = yield mysql_app_pool.get_one(sql.format(
        c_id, phone, current_time
    ))
    raise gen.Return(res[0])


@gen.coroutine
def get_zan_id(c_id, phone):
    sql = (
        "select id from app_circle_zan where c_id={} and phone='{}'"
    )
    res = yield mysql_app_pool.get_one(sql.format(
        c_id, phone
    ))
    raise gen.Return(res[0])


@gen.coroutine
def add_psuh_task(type, info_id):
    current_time = timetool.get_timestamp()
    sql = (
        "insert into app_push_task (info_id, type, status, create_time) values ({}, {}, 0, {})"
    )
    res = yield mysql_app_pool.insert_one(sql.format(
        info_id, type, current_time
    ))
    raise gen.Return(res)


@gen.coroutine
def check_zan(c_id, phone):
    sql = (
        "select 1 from app_circle_zan where c_id = {} and phone='{}'"
    )
    t = yield mysql_app_pool.get_one(sql.format(
        c_id, phone
    ))
    res = 1 if t else 0
    raise gen.Return(res)


@gen.coroutine
def get_circle_list_all(c_id, page_count, city, phone, couch):
    param = "and a.id < {}".format(c_id) if c_id else ""
    sql = (
        "select a.id as c_id, a.phone, a.address, b.nick_name, b.avatar, a.text as content, a.images,a.create_time "
        "from app_circle_info a, app_user_info b where a.phone=b.phone "
        # u"and (a.city='{}' or a.belong_area='{}') {} "
        u" {} "
        "and a.is_using = 1 "
        "and a.is_top = 0 "
        "and a.couch = {} "
        "order by a.create_time desc limit {}"
    )
    res = yield mysql_app_pool.get_all(sql.format(
        param, couch, page_count
    ), cursor='dict')
    for item in res:
        reply_cnt = yield get_reply_cnt(item['c_id'])
        zan_cnt = yield get_zan_cnt(item['c_id'])
        item['is_zan'] = yield check_zan(item['c_id'], phone)
        item['reply_cnt'] = reply_cnt
        item['zan_cnt'] = zan_cnt
        item['images'] = item['images'].split()

    raise gen.Return(res)


@gen.coroutine
def get_my_circle_list(phone, c_id, page_count, couch):
    param = "and a.id < {}".format(c_id) if c_id else ""
    sql = (
        "select a.id as c_id, a.phone, a.address, b.nick_name, b.avatar, a.text as content, a.images,a.create_time "
        "from app_circle_info a, app_user_info b where a.phone=b.phone "
        "and a.phone = '{}'"
        "and a.couch = {} "
        "and a.is_using = 1 {} "
        "order by a.create_time desc limit {}"
    )
    res = yield mysql_app_pool.get_all(sql.format(
        phone, couch, param, page_count
    ), cursor='dict')
    for item in res:
        reply_cnt = yield get_reply_cnt(item['c_id'])
        zan_cnt = yield get_zan_cnt(item['c_id'])
        item['is_zan'] = yield check_zan(item['c_id'], phone)
        item['reply_cnt'] = reply_cnt
        item['zan_cnt'] = zan_cnt
        item['images'] = item['images'].split()

    raise gen.Return(res)


@gen.coroutine
def get_circle_detail(c_id, phone):
    sql = (
        "select a.id as c_id, a.phone, b.nick_name, b.avatar, a.text as content, a.images,a.create_time "
        "from app_circle_info a, app_user_info b where a.phone=b.phone "
        "and a.is_using = 1 and a.id={}"
    )
    res = yield mysql_app_pool.get_one(sql.format(
        c_id
    ), cursor='dict')
    if res:
        # reply_cnt = yield get_reply_cnt(res['c_id'])
        # zan_cnt = yield get_zan_cnt(res['c_id'])
        res['is_zan'] = yield check_zan(res['c_id'], phone)
        res['reply_cnt'] = 0
        res['zan_cnt'] = 0
        res['images'] = res['images'].split()
    raise gen.Return(res)


@gen.coroutine
def get_reply_list(c_id, page_count, reply_id):
    param = "and a.id > {}".format(reply_id) if reply_id else ""
    sql = (
        "select a.id as reply_id, a.phone, a.to_phone, a.content, a.images, a.create_time, a.to_reply_id "
        "from app_circle_reply a where 1=1 "
        u"and a.c_id = {} {} "
        "and a.is_using = 1 "
        "order by a.create_time limit {}"
    )
    res = yield mysql_app_pool.get_all(sql.format(
        c_id, param, page_count
    ), cursor='dict')
    for item in res:
        item['images'] = item['images'].split()
    raise gen.Return(res)


@gen.coroutine
def get_reply_cnt(c_id):
    sql = (
        "select count(*) as cnt "
        "from app_circle_reply a where 1=1 "
        u"and a.c_id = {} and is_using=1"
    )
    res = yield mysql_app_pool.get_one(sql.format(
        c_id
    ), cursor='dict')
    raise gen.Return(res['cnt'])


@gen.coroutine
def get_zan_cnt(c_id):
    sql = "select count(*) as cnt from app_circle_zan where c_id = {}"
    res = yield mysql_app_pool.get_one(sql.format(
        c_id
    ), cursor='dict')
    raise gen.Return(res['cnt'])


@gen.coroutine
def get_user_info_with_phone(phone):
    sql = "select nick_name, avatar, phone from app_user_info where phone = '{}'"
    res = yield mysql_app_pool.get_one(sql.format(
        phone
    ), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_reply_info(reply_id):
    sql = "select content, images from app_circle_reply where id = {}"
    res = yield mysql_app_pool.get_one(sql.format(
        reply_id
    ), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_phone_with_cid(c_id):
    sql = "select phone from app_circle_info where id = {}"
    res = yield mysql_app_pool.get_one(sql.format(
        c_id
    ), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def sava_zan_2_db(c_id, phone, to_phone, current_time, couch):
    sql = u"insert into app_circle_zan (c_id, phone, to_phone, pubtime,couch) values ({}, '{}', '{}', {}, {})"
    yield mysql_app_pool.insert_one(sql.format(
        c_id, phone, to_phone, current_time, couch
    ))
    raise gen.Return(True)


@gen.coroutine
def cancel_zan(c_id, phone):
    sql = u"delete from app_circle_zan where c_id = {} and phone = '{}'"
    yield mysql_app_pool.update(sql.format(
        c_id, phone
    ))
    raise gen.Return(True)


@gen.coroutine
def get_zan_list_with_c_id(c_id, zan_id, page_count):
    param = "and a.id < {}".format(zan_id) if zan_id else ""
    sql = (
        "select a.id as zan_id, a.phone, a.c_id, b.avatar, b.nick_name,a.pubtime from app_circle_zan a, app_user_info b where a.c_id = {} "
        "and a.phone=b.phone {} "
        "order by a.id desc limit {}"
    )
    res = yield mysql_app_pool.get_all(sql.format(c_id, param, page_count), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def check_circle_exists(c_id):
    sql = "select 1 from app_circle_info where id = {} and is_using=1"
    res = yield mysql_app_pool.get_one(sql.format(
        c_id
    ), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_circle_info_with_cid(c_id):
    sql = "select * from app_circle_info where id = {}"
    res = yield mysql_app_pool.get_one(sql.format(
        c_id
    ), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def delete_circle_from_db(c_id):
    sql = "update app_circle_info set is_using=0 where id = {}"
    res = yield mysql_app_pool.update(sql.format(
        c_id
    ))
    raise gen.Return(res)


@gen.coroutine
def delete_reply_from_db(c_id):
    sql = "delete from app_circle_reply where c_id = {}"
    res = yield mysql_app_pool.delete(sql.format(
        c_id
    ))
    raise gen.Return(res)


@gen.coroutine
def delete_zan_from_db(c_id):
    sql = "delete from app_circle_zan where c_id = {}"
    res = yield mysql_app_pool.delete(sql.format(
        c_id
    ))
    raise gen.Return(res)


@gen.coroutine
def get_reply_images(c_id):
    sql = "select images from app_circle_reply where c_id = {}"
    res = yield mysql_app_pool.get_all(sql.format(
        c_id
    ), cursor='dict')
    img_list = []
    for item in res:
        img_list += item['images'].split()
    raise gen.Return(img_list)


def remove_img_files(img_list):
    upload_path = settings.IMG_BASE_PATH + 'app/'
    for item in img_list:
        os.remove(upload_path + item.split('/')[-1])


@gen.coroutine
def get_zan_me_list(phone, zan_id, page_count, couch):
    param = "and a.id < {}".format(zan_id) if zan_id else ""
    sql = (
        "select a.id as zan_id, a.pubtime, a.c_id, b.nick_name, b.avatar from app_circle_zan a, "
        "app_user_info b where a.to_phone = '{}' "
        "and a.phone=b.phone "
        "and a.couch={} "
        "{} "
        "order by a.id desc limit {}"
    )
    i_zan_list = yield mysql_app_pool.get_all(sql.format(
        phone, couch, param, page_count
    ), cursor='dict')
    for item in i_zan_list:
        sql1 = "select text as content, images from app_circle_info where id = {}"
        content = yield mysql_app_pool.get_one(sql1.format(
            item['c_id']
        ), cursor='dict')
        if content:
            item['to_content'] = content['content']
            tmp = content['images'].split()
            item['to_image'] = tmp[0] if tmp else ""
        else:
            item['to_content'] = ""
            item['to_image'] = ""

    raise gen.Return(i_zan_list)


@gen.coroutine
def get_i_reply_list(phone, reply_id, page_count, couch):
    param = "and a.id < {}".format(reply_id) if reply_id else ""
    sql = (
        "select a.id as reply_id, a.c_id, a.content, a.create_time, a.images, a.to_reply_id, b.nick_name, b.avatar  from app_circle_reply a, app_user_info b "
        "where a.phone = '{}' "
        "and a.to_phone=b.phone "
        "and a.couch={} "
        "{} "
        "order by a.id desc limit {}"
    )
    i_reply_list = yield mysql_app_pool.get_all(sql.format(
        phone, couch, param, page_count
    ), cursor='dict')
    for item in i_reply_list:
        item['images'] = item['images'].split()
        if item['to_reply_id']:
            sql1 = "select content from app_circle_reply where id = {}"
            content = yield mysql_app_pool.get_one(sql1.format(
                item['to_reply_id']
            ), cursor='dict')
            item['to_content'] = content['content'] if content else ""
        else:
            sql1 = "select text as content from app_circle_info where id = {}"
            content = yield mysql_app_pool.get_one(sql1.format(
                item['c_id']
            ), cursor='dict')

            item['to_content'] = content['content'] if content else ""

    raise gen.Return(i_reply_list)


@gen.coroutine
def get_reply_me_list(phone, reply_id, page_count,couch):
    param = "and a.id < {}".format(reply_id) if reply_id else ""
    sql = (
        "select a.id as reply_id, a.c_id, a.content, a.create_time, a.images,a.to_reply_id, b.nick_name, b.avatar  from app_circle_reply a, app_user_info b "
        "where a.to_phone = '{}' "
        "and a.phone=b.phone "
        "and a.couch={} "
        "{} "
        "order by a.id desc limit {}"
    )
    i_reply_list = yield mysql_app_pool.get_all(sql.format(
        phone, couch, param, page_count
    ), cursor='dict')
    for item in i_reply_list:
        item['images'] = item['images'].split()
        if item['to_reply_id']:
            sql1 = "select content from app_circle_reply where id = {}"
            content = yield mysql_app_pool.get_one(sql1.format(
                item['to_reply_id']
            ), cursor='dict')
            item['to_content'] = content['content'] if content else ""
        else:
            sql1 = "select text as content from app_circle_info where id = {}"
            content = yield mysql_app_pool.get_one(sql1.format(
                item['c_id']
            ), cursor='dict')

            item['to_content'] = content['content'] if content else ""

    raise gen.Return(i_reply_list)


@gen.coroutine
def get_notice_circle_list():
    sql = (
        "select id as news_id, title,create_time as pubtime,content from app_news_info "
        "where type=1 and is_first=1 order by pubtime desc limit 3"
    )
    info = yield mysql_app_pool.get_all(sql, cursor='dict')
    for item in info:
        content = item['content']
        pat = '<img src="(.*?)"'
        reslist = re.findall(pat, content, re.M)
        print reslist
        soup = BeautifulSoup(content, 'html5lib')
        abstract = soup.get_text()
        item['img_url'] = reslist[0] if reslist else ""
        item['abstract'] = abstract[:50]
        del item['content']
    raise gen.Return(info)


@gen.coroutine
def get_top_circle_list(phone, city, couch):
    sql = (
        "select a.id as c_id, a.phone, a.address, b.nick_name, b.avatar, a.text as content, a.images,a.create_time "
        "from app_circle_info a, app_user_info b where a.phone=b.phone "
        # u"and (a.city='{}' or a.belong_area='{}') "
        "and a.is_using = 1 "
        "and a.is_top = 1 "
        "and a.couch = {} "
        "order by a.create_time desc limit 3"
    )
    res = yield mysql_app_pool.get_all(sql.format(couch), cursor='dict')
    for item in res:
        reply_cnt = yield get_reply_cnt(item['c_id'])
        zan_cnt = yield get_zan_cnt(item['c_id'])
        item['is_zan'] = yield check_zan(item['c_id'], phone)
        item['reply_cnt'] = reply_cnt
        item['zan_cnt'] = zan_cnt
        item['images'] = item['images'].split()
    raise gen.Return(res)